using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Western
{
    public class Cash : DataAccess
    {
        public Cash(string db_str)
        {
            CreateConnection(db_str);
        }
        public DataSet GetCashBalance(string account_no, string clnt_type, DateTime history_date)
        {
            DataSet cash_balance = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT * FROM CashAccount WHERE client_id=@account_no";
                //cmd.Parameters.Add("@account_no", SqlDbType.NVarChar, 64).Value = account_no;
                cmd.CommandText = "sp_sel_CashBalance";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = int.MaxValue;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@ClntCode", SqlDbType.VarChar, 20).Value = account_no;
                cmd.Parameters.Add("@ClntType", SqlDbType.VarChar, 10).Value = clnt_type;
                cmd.Parameters.Add("@TrxnDate", SqlDbType.DateTime).Value = history_date;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(cash_balance);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cash_balance;
        }

        public DataSet GetCashPayable(string account_no, DateTime history_date)
        {
            DataSet cash_balance = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT * FROM CashAccount WHERE client_id=@account_no";
                //cmd.Parameters.Add("@account_no", SqlDbType.NVarChar, 64).Value = account_no;
                cmd.CommandText = "sp_sel_PayableSettle";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@client_id", SqlDbType.VarChar, 10).Value = account_no;
                cmd.Parameters.Add("@log_date", SqlDbType.DateTime).Value = history_date;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(cash_balance);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cash_balance;
        }

        public DataSet GetCashAdvance(DateTime settle_date, string account_no)
        {
            DataSet cash_advance = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();                
                cmd.CommandText = "sp_sel_EFCashAdvance";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@client_id", SqlDbType.VarChar, 10).Value = account_no;
                cmd.Parameters.Add("@settle_date", SqlDbType.DateTime).Value = settle_date;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(cash_advance);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cash_advance;
        }

        public DataSet GetCashTrans(DateTime from_date, DateTime to_date, string tran_type, 
            string status, string client_id)
        {
            DataSet cash_balance = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_CashTrans";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@FROM_DATE", SqlDbType.DateTime).Value = from_date;
                cmd.Parameters.Add("@TO_DATE", SqlDbType.DateTime).Value = to_date;
                cmd.Parameters.Add("@CLIENT_ID", SqlDbType.VarChar, 20).Value = client_id;
                cmd.Parameters.Add("@TRAN_TYPE", SqlDbType.VarChar, 4).Value = tran_type;
                cmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 1).Value = status;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(cash_balance);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cash_balance;
        }

        public DataSet GetCashSettlement(DateTime tran_date, string client_id)
        {
            DataSet cash_balance = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_EFCashSettlement";
                cmd.CommandType = CommandType.StoredProcedure;                
                cmd.Parameters.Add("@tran_date", SqlDbType.DateTime).Value = tran_date;
                cmd.Parameters.Add("@account_no", SqlDbType.VarChar, 20).Value = client_id;                
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(cash_balance);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cash_balance;
        }

        public decimal CreateCashTrans(DateTime tran_date, string time_stamp, string tran_type,
            string client_id, decimal amount, string currency, string description,
            string module, string created_by, DateTime date_created, decimal tran_num)
        {
            decimal tran_numer = 0;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_ins_CashTrans";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AMOUNT", SqlDbType.Decimal).Value = amount;
                cmd.Parameters.Add("@TRAN_DATE", SqlDbType.DateTime).Value = tran_date;
                cmd.Parameters.Add("@TIMESTAMP", SqlDbType.VarChar, 8).Value = time_stamp;
                cmd.Parameters.Add("@TRAN_TYPE", SqlDbType.VarChar, 4).Value = tran_type;
                cmd.Parameters.Add("@CLIENT_ID", SqlDbType.VarChar, 20).Value = client_id;
                cmd.Parameters.Add("@CURRENCY", SqlDbType.VarChar, 3).Value = currency;
                cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarChar, 255).Value = description;
                cmd.Parameters.Add("@MODULE", SqlDbType.VarChar, 2).Value = module;
                cmd.Parameters.Add("@CREATED_BY", SqlDbType.VarChar, 50).Value = created_by;
                cmd.Parameters.Add("@DATE_CREATED", SqlDbType.DateTime).Value = date_created;
                cmd.Parameters.Add("@TRAN_NUM", SqlDbType.Decimal).Value = tran_num;
                conn.Open();
                tran_numer = Convert.ToDecimal(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

            return tran_numer;
        }
        public void CreateAccount(string account_no, string currency, decimal withdraw_amount,
            decimal deposit_amount, decimal blockade_amount, decimal payable_amount, string status)
        {
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_ins_CashAccount";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@client_id", SqlDbType.VarChar, 20).Value = account_no;
                cmd.Parameters.Add("@currency", SqlDbType.VarChar, 10).Value = currency;
                cmd.Parameters.Add("@withdraw_amount", SqlDbType.Decimal).Value = withdraw_amount;
                cmd.Parameters.Add("@deposit_amount", SqlDbType.Decimal).Value = deposit_amount;
                cmd.Parameters.Add("@blockade_amount", SqlDbType.Decimal).Value = blockade_amount;
                cmd.Parameters.Add("@payable_amount", SqlDbType.Decimal).Value = payable_amount;
                cmd.Parameters.Add("@status", SqlDbType.VarChar, 1).Value = status;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        public string ApproveCashTrans(decimal tran_num, string modified_by, DateTime date_modified)
        {
            string err_code = string.Empty;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_upd_CashTrans";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@tran_num", SqlDbType.Decimal).Value = tran_num;
                cmd.Parameters.Add("@modified_by", SqlDbType.VarChar, 50).Value = modified_by;
                cmd.Parameters.Add("@date_modified", SqlDbType.DateTime).Value = date_modified;
                
                conn.Open();
               err_code = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return err_code;
        }
    }
}
